package com.cpkf.util.iTextPDF;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Iterator;

import org.json.JSONArray;
import org.json.JSONException;
import org.json.JSONObject;

/**
 * DB util, this can connect the DB.
 * This class can connect to the database through the necessary parameters, it can execute the query and modify operation, 
 * and system will return ResultSet object.
 * Call different databases, you should add the appropriate pom dependency, such as: calling mysql, you need to add mysql-connector-java
 *
 * @author Hyman Hu
 * @since  2014-6-23
 * Copyright SHOP.COM: all rights reserved worldwide
 */
public class DatabaseUtil {

	private Connection connection;
	private Statement st;
	private PreparedStatement ps;

	/**
	 * @param driverString    Driver path name, like 'com.mysql.jdbc.Driver'
	 * @param urlString       Connect information, like 'jdbc:mysql://localhost:3306/notepad?characterEncoding=utf-8'
	 * @param userName        DB login user name
	 * @param password        DB login password
	 * @throws SQLException   Sql about exception when create connection
	 */
	public DatabaseUtil(String driverString, String urlString, String userName, String password)
			throws SQLException {
		try {
			Class.forName(driverString);
			connection = DriverManager.getConnection(urlString, userName, password);
		} catch (Exception e) {
			throw new SQLException("Database driver not found.");
		}
	}

	/**
	 * @param driverString      Driver path name, like 'com.mysql.jdbc.Driver'
	 * @param urlString         Connect information, like 'jdbc:mysql://localhost:3306/notepad?characterEncoding=utf-8&user=root&password='
	 * @throws SQLException     Sql about exception when create connection
	 */
	public DatabaseUtil(String driverString, String urlString) throws SQLException {
		try {
			Class.forName(driverString);
			connection = DriverManager.getConnection(urlString);
		} catch (Exception e) {
			throw new SQLException("Database driver not found.");
		}

	}

	/**
	 * close connection 
	 * 
	 * @throws SQLException
	 */
	public void close() throws SQLException {
		if (st != null) {
			st.close();
		}

		if (ps != null) {
			ps.close();
		}

		if (connection != null) {
			connection.close();
		}
	}

	/**
	 * get result by sql string
	 * 
	 * @param sql             Query sql string, like 'SELECT * FROM account WHERE available = TRUE;'
	 * @return ResultSet      
	 * @throws SQLException
	 */
	public ResultSet query(String sql) throws SQLException {
		PreparedStatement ps = connection.prepareStatement(sql);
		return ps.executeQuery();
	}

	/**
	 * update db
	 * 
	 * @param expression      execute sql string for delete, update, add
	 * @throws SQLException
	 */
	public void update(String expression) throws SQLException {
		st = connection.createStatement();
		int i = st.executeUpdate(expression);
		close();
		if (i == -1) {
			throw new SQLException("db error : " + expression);
		}
	}
	

	public static void main(String[] args) throws SQLException, JSONException {
//		DatabaseUtil dc = new DatabaseUtil("com.mysql.jdbc.Driver", "jdbc:mysql://localhost:3306/notepad?characterEncoding=utf-8", "root", "");
		DatabaseUtil dc = new DatabaseUtil("com.mysql.jdbc.Driver", "jdbc:mysql://localhost:3306/notepad?characterEncoding=utf-8&user=root&password=");
		ResultSet rs = dc.query("SELECT * FROM account WHERE available = TRUE;");
		System.out.println(DataConvertUtil.convertToList(rs));
		rs = dc.query("SELECT * FROM account WHERE available = TRUE;");
		JSONArray jSONArray = DataConvertUtil.convertToJSONArray(rs);
		System.out.println(jSONArray);
		for (int i = 0; i < jSONArray.length(); i++) {
			JSONObject jsonObject = jSONArray.getJSONObject(i);
			Iterator<String> iterator = jsonObject.keys();
			while (iterator.hasNext()) {
				String key = iterator.next();
				System.out.println(key + "--" + jsonObject.get(key));
			}
			System.out.println("---------------------------");
		}
		dc.close();
	}
}
